﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtlity;
using Models;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class PJOrderStartService
    {
        #region 新增
        public bool InsertInfo(PJOrderStart main)
        {
            string mainSql = "insert into PJOrderStarts(WorkParentId,SerialNum,OrderType,OrderPurpose,Person)";
            mainSql += " values(@WorkParentId,@SerialNum,@OrderType,@OrderPurpose,@Person)";
            SqlParameter[] mainParam = new SqlParameter[]
            {
                new SqlParameter("@WorkParentId",main.WorkParentId),
                new SqlParameter("@SerialNum",main.SerialNum),
                new SqlParameter("@OrderType",main.OrderType),
                new SqlParameter("@OrderPurpose",main.OrderPurpose),
                new SqlParameter("@Person",main.Person)
            };
            string detailSql = "insert into PJOrderChildStarts(Row,SerialNum,CustOrDepart,CustomerOrder,";
            detailSql += "Maktx,Matnr,Number,Mold,SapOrder,Ps)";
            detailSql += " values(@Row,@SerialNum,@CustOrDepart,@CustomerOrder,";
            detailSql += "@Maktx,@Matnr,@Number,@Mold,@SapOrder,@Ps)";
            List<SqlParameter[]> detailParam = new List<SqlParameter[]>();
            for (int i = 0; i < main.ChildList.Count; i++)
            {
                detailParam.Add(new SqlParameter[]
                {
                    new SqlParameter("@Row",i+1),
                    new SqlParameter("@SerialNum",main.ChildList[i].SerialNum),
                    new SqlParameter("@CustOrDepart",main.ChildList[i].CustOrDepart),
                    new SqlParameter("@CustomerOrder",main.ChildList[i].CustomerOrder),
                    new SqlParameter("@Maktx",main.ChildList[i].Maktx),
                    new SqlParameter("@Matnr",main.ChildList[i].Matnr),
                    new SqlParameter("@Number",main.ChildList[i].Number),
                    new SqlParameter("@Mold",main.ChildList[i].Mold),
                    //new SqlParameter("@ConfirmDate",main.ChildList[i].ConfirmDate),
                    new SqlParameter("@SapOrder",main.ChildList[i].SapOrder),
                    new SqlParameter("@Ps",main.ChildList[i].Ps)
                });
            }
            return SQLHelper.UpdateByTran(mainSql, mainParam, detailSql, detailParam);
        }
        public bool InsertChildInfo(PJOrderStart main)
        {
            string detailSql = "insert into PJOrderChildStarts(Row,SerialNum,CustOrDepart,CustomerOrder,";
            detailSql += "Maktx,Matnr,Number,Mold,SapOrder,Ps)";
            detailSql += " values(@Row,@SerialNum,@CustOrDepart,@CustomerOrder,";
            detailSql += "@Maktx,@Matnr,@Number,@Mold,@SapOrder,@Ps)";
            List<SqlParameter[]> detailParam = new List<SqlParameter[]>();
            for (int i = 0; i < main.ChildList.Count; i++)
            {
                detailParam.Add(new SqlParameter[]
                {
                    new SqlParameter("@Row",main.ChildList[i].Row),
                    new SqlParameter("@SerialNum",main.ChildList[i].SerialNum),
                    new SqlParameter("@CustOrDepart",main.ChildList[i].CustOrDepart),
                    new SqlParameter("@CustomerOrder",main.ChildList[i].CustomerOrder),
                    new SqlParameter("@Maktx",main.ChildList[i].Maktx),
                    new SqlParameter("@Matnr",main.ChildList[i].Matnr),
                    new SqlParameter("@Number",main.ChildList[i].Number),
                    new SqlParameter("@Mold",main.ChildList[i].Mold),
                    new SqlParameter("@SapOrder",main.ChildList[i].SapOrder),
                    new SqlParameter("@Ps",main.ChildList[i].Ps)
                });
            }
            return SQLHelper.UpdateByTran(null, null, detailSql, detailParam);
        }
        #endregion

        #region 更改
        public bool UpdateInfo(PJOrderStart main)
        {
            string mainSql = "update PJOrderStarts set Person=@Person where WorkparentId=@WorkParentId";
            SqlParameter[] mainParam = new SqlParameter[]
            {
                new SqlParameter("@Person",main.Person),
                new SqlParameter("@WorkparentId",main.WorkParentId)
            };
            string detailSql = "update PJOrderChildStarts set CustOrDepart=@CustOrDepart,";
            detailSql += "CustomerOrder=@CustomerOrder,Maktx=@Maktx,Matnr=@Matnr,Number=@Number,";
            detailSql += "Mold=@Mold,Ps=@Ps";
            detailSql += " where SerialNum=@SerialNum and Row=@Row";
            List<SqlParameter[]> detailParam = new List<SqlParameter[]>();
             for (int i = 0; i < main.ChildList.Count; i++)
            {
                detailParam.Add(new SqlParameter[]
                {
                    new SqlParameter("@Row",i+1),
                    new SqlParameter("@SerialNum",main.ChildList[i].SerialNum),
                    new SqlParameter("@CustOrDepart",main.ChildList[i].CustOrDepart),
                    new SqlParameter("@CustomerOrder",main.ChildList[i].CustomerOrder),
                    new SqlParameter("@Maktx",main.ChildList[i].Maktx),
                    new SqlParameter("@Matnr",main.ChildList[i].Matnr),
                    new SqlParameter("@Number",main.ChildList[i].Number),
                    new SqlParameter("@Mold",main.ChildList[i].Mold),
                    //new SqlParameter("@ConfirmDate",main.ChildList[i].ConfirmDate),
                    //new SqlParameter("@SapOrder",main.ChildList[i].SapOrder),
                    new SqlParameter("@Ps",main.ChildList[i].Ps)
                });
            }
            return SQLHelper.UpdateByTran(mainSql, mainParam, detailSql, detailParam);
        }
        public int UpdateDate(DateTime confirmDate,string matnr,string serialNum,int row)
        {
            string sql = "update PJOrderChildStarts set ConfirmDate=@ConfirmDate,Matnr=@Matnr";
            sql += " where SerialNum=@SerialNum and Row=@Row";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@ConfirmDate",confirmDate),
                new SqlParameter("@Matnr",matnr),
                new SqlParameter("@SerialNum",serialNum),
                new SqlParameter("@Row",row)
            };
            return SQLHelper.Update(sql, param);
        }
        public int UpdateSapOrder(string sapOrder,string serialNum,int row)
        {
            string sql = "update PJOrderChildStarts set SapOrder=@SapOrder";
            sql += " where SerialNum=@SerialNum and Row=@Row";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@SapOrder",sapOrder),
                new SqlParameter("@SerialNum",serialNum),
                new SqlParameter("@Row",row)
            };
            return SQLHelper.Update(sql, param);
        }
        public int UpdateStatues(int workParentId)
        {
            string sql = "update PJOrderStarts set Statues=@Statues";
            sql += " where WorkParentId=@WorkParentId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Statues","1"),
                new SqlParameter("@WorkParentId",workParentId)
            };
            return SQLHelper.Update(sql, param);
        }
        #endregion

        #region 删除
        public int DelChildList(string serialNum,int row)
        {
            string sql = "delete from PJOrderChildStarts where SerialNum=@SerialNum and Row>@Row";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@SerialNum",serialNum),
                new SqlParameter("@Row",row)
            };
            return SQLHelper.Update(sql, param);
        }
        #endregion

        #region 查询
        /// <summary>
        /// 获取最大Id行的流水号
        /// </summary>
        /// <returns></returns>
        public string GetLastSerialNum(string orderType,string orderPurpose)
        {
            string sql = "select SerialNum from PJOrderStarts";
            sql += " where Id=(select Max(Id) from PJOrderStarts";
            sql += " where  OrderType=@OrderType and OrderPurpose=@OrderPurpose)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@OrderType",orderType),
                new SqlParameter("@OrderPurpose",orderPurpose)
            };
            return Convert.ToString(SQLHelper.GetSingleResult(sql,param));
        }

        public PJOrderStart GetOrderStart(int workParentId)
        {
            string sql = "select * from PJOrderStarts";
            sql += " left join PJOrderChildStarts on PJOrderStarts.SerialNum=PJOrderChildStarts.SerialNum";
            sql += " where WorkParentId=@WorkParentId";
            PJOrderStart objPJOrderStart = null;
            List<PJOrderChildStart> list = new List<PJOrderChildStart>();
            SqlDataReader objReader = SQLHelper.GetReader(sql, new SqlParameter[] { new SqlParameter("@WorkParentId", workParentId) });
            while(objReader.Read())
            {
                objPJOrderStart = new PJOrderStart()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    WorkParentId = Convert.ToInt32(objReader["WorkParentId"]),
                    SerialNum = objReader["SerialNum"].ToString(),
                    OrderType = objReader["OrderType"].ToString(),
                    OrderPurpose = objReader["OrderPurpose"].ToString(),
                    Person = objReader["Person"] is DBNull ? "" : objReader["Person"].ToString(),
                    Statues = objReader["Statues"].ToString() == "0" ? "未同步" : "已同步",
                    ChildList = new List<PJOrderChildStart>()
                };
                list.Add(new PJOrderChildStart()
                {
                    Row = Convert.ToInt32(objReader["Row"]),
                    SerialNum = objReader["SerialNum"].ToString(),
                    CustOrDepart = objReader["CustOrDepart"].ToString(),
                    CustomerOrder = objReader["CustomerOrder"].ToString(),
                    Maktx = objReader["Maktx"].ToString(),
                    Matnr = objReader["Matnr"] is DBNull ? "" : objReader["Matnr"].ToString(),
                    Number = Convert.ToInt32(objReader["Number"]),
                    Mold = objReader["Mold"].ToString(),
                    ConfirmDate = objReader["ConfirmDate"] is DBNull ? Convert.ToDateTime("1900-01-01") : Convert.ToDateTime(objReader["ConfirmDate"]),
                    SapOrder = objReader["SapOrder"].ToString(),
                    Ps = objReader["Ps"].ToString()
                });
            }
            if(objPJOrderStart!=null)
            {
                objPJOrderStart.ChildList = list;
            }
            objReader.Close();
            return objPJOrderStart;
        }

        public int IsNoOrderStart(int workParentId)
        {
            string sql = "select count(*) from PJOrderStarts where WorkParentId=@WorkParentId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@WorkParentId",workParentId)
            };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }
        public int GetChildListNum(string serialNum)
        {
            string sql = "select count(*) from PJOrderChildStarts where SerialNum=@SerialNum";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@SerialNum",serialNum)
            };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }
        #endregion
    }
}
